﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Configuration;

namespace PhysicalDataProvider
{
    public class SqlHelper
    {
        public static DbConnection Connection
        {
            get
            {
                ConnectionStringSettings setting
                    = ConfigurationManager.ConnectionStrings
                    ["AccessProvider"];

                DbProviderFactory factory
                    = DbProviderFactories.GetFactory
                    (setting.ProviderName);

                DbConnection connection = factory.CreateConnection();

                connection.ConnectionString = setting.ConnectionString;

                return connection;
            }
        }

        public static int ExecuteNonQuery(string sql,
            params DbParameter[] commandParameters)
        {
            using (DbConnection connection = Connection)
            {
                DbCommand cmd = connection.CreateCommand();

                cmd.Parameters.AddRange(commandParameters);
                cmd.CommandText = sql;

                connection.Open();

                int val = cmd.ExecuteNonQuery();

                connection.Close();

                return val;
            }
        }

        public static Object ExecuteScalar(string sql,
           params DbParameter[] commandParameters)
        {
            using (DbConnection connection = Connection)
            {
                DbCommand cmd = connection.CreateCommand();

                cmd.Parameters.AddRange(commandParameters);
                cmd.CommandText = sql;

                connection.Open();

                object obj = cmd.ExecuteScalar();

                connection.Close();

                return obj;
            }
        }
    }
}
